Bricks Without Clay￼

Demystifying PolarisTransactions

Daniel Messer

Tanglewood Hill Studio

2

Daniel Messer

Bricks Without Clay: Demystifying PolarisTransactions

By Daniel Messer

cyberpunklibrarian@protonmail.com

Tanglewood Hill Studio

tanglewoodhill.studio

Cover image courtesy of Pixabay

https://pixabay.com/photos/city-night-cyberpunk-neon-6389129/

Creator: Baggeb

https://pixabay.com/users/baggeb-15600954/

Copyright 2023 through Creative Commons

Attribution-NonCommercial-ShareAlike 4.0 International

(CC BY-NC-SA 4.0)

creativecommons.org/licenses/by-nc-sa/4.0/

Bricks Without Clay

3

Table of Contents

Dedication

4

Prologue

6

Chapter One - Waypoints

9

Chapter Two - The View from the Bottom: TransactionHeaders

13

Chapter Three - Just the Facts, Ma’am: TransactionTypes

18

Chapter Four - Cookbook: Part One

26

Chapter Five - The Devil, the Data, and the Deep Blue Details

33

Chapter Six - Cookbook: Part Two

43

Chapter Seven - The Codes Under the Codes Beneath the Type

54

Chapter Eight - String Theory

63

Chapter Nine - Cookbook: Part Three

72

Chapter Ten - Polaris Calling

82

Chapter Eleven - Where My Story Ends and Yours Begins

91

About the Author

96

4

Daniel Messer

￼

Dedication

To Yvette, Sharon, Theresa, Howard, Holly, Jeremy, Tony, Karrie,

Coreen, Carrie (a different Carrie than the first Karrie), and all the

other librarians who took a chance on me. Y’all didn’t have to do

that, but I’m glad you did.

And to the amazing folks within the Innovative Users Group. You

are an amazing group of librarians and a beautiful bunch of nerds.

Bricks Without Clay

5

Data is not information,

information is not knowledge,

knowledge is not understanding

understanding is not wisdom.

~Clifford Stoll

Interlibrary loan is my sexual preference.

~Merlin Mann

6

Daniel Messer

￼

Prologue

No one reads these things and they exist to get in the way

of the information further ahead in the book. But in the interests of

the reader, I think a few paragraphs won’t hurt. Because, after all,

you should know whether or not this book is for you.

Are you a newer Polaris ILS administrator and/or are you

unfamiliar with the PolarisTransactions database? Do you know

anything about T-SQL and Microsoft SQL Server, anything at all?

If so, welcome! You’re well within the target demographic and

you’re exactly who I’d aimed for if I bought ads for this book on

Instagram. Or maybe you’ve never even heard of the

PolarisTransactions database, or it came up in passing and you’re

wondering what the heck it is and, more importantly, what it does.

Well, there are at least three basic databases in your average

Polaris ILS installation: Polaris, PolarisTransactions, and Results.

You may see more of them if you fire up SQL Server Management

Studio or Azure Data Studio, but I can guarantee you’ll see those

three at the very least.

The Polaris database can best be described as “what’s happening

now?” It’s full of data about your library system, your patrons,

your items, your patron’s fines and fees, your bibliographic data,

Bricks Without Clay

7

and more. The Polaris database is huge and its a rapidly changing

place wherein your library operates.

The Results database holds on to data that is, no surprises,

the result of various operations within your system. You’ll find

plenty of information about patron notifications and, importantly,

your notification queue. That queue lists all of the notifications that

have yet to go out and that’s probably the most important table in

this database.

Meanwhile, the PolarisTransactions database can be

defined as “What happened?” It’s treasure trove of data that will

provide statistics, forensics, tracking, logs, and so much more.

While you can look to certain tables in the Polaris database for

item circulation stats, those tables are almost always set to clear

data after a certain amount of time. With a few exceptions, the

PolarisTransactions database never clears data. It’s an archive that

extends back to the very moment you came online with your

Polaris ILS. Needless to say, it’s important.

But it’s weird.

You will never need to run an UPDATE, DELETE, or

INSERT command on the PolarisTransactions database. If you find

yourself writing a query to do so, close out your SQL editor and go

outside. Stare out into the distance and re-evaluate the decisions

you’ve made that brought your life to this point. More than

anything, the feature that makes PolarisTransactions so odd is its

very structure. There are codes, references to codes, references to

references to codes. Sometimes the data is right there, often you’ll

need to throw other tables at it to make sense of it all. Writing SQL

targeting the PolarisTransactions means that your FROM statement

will almost always be followed by a JOIN. You’ll link to other

tables in PolarisTransactions, and you’ll find yourself linking to

tables in the other two databases too. That makes sense, because

8

Daniel Messer

the PolarisTransactions database is there to keep track of what’s

happening in both of those databases.

If you’re interested in getting that sweet, delicious data out

of the PolarisTransactions database, you need to have an

understanding how everything links together and you’ll need to

know a bit of SQL. Beyond that, I can promise you one thing as we

move forward: Once you have a basic understanding of how this

database is structured and how it folds in upon itself and works

with the other databases, everything becomes much easier.

So as Coolio once said, “Come on, y’all, let’s take a ride.”

Daniel Messer

Alvaton, Kentucky

Bricks Without Clay

9

￼

Chapter One - Waypoints

Y

a know, it’s funny. The primary Polaris database has over

1,500 tables and contains all the data for what’s happening

now in your Polaris system. And most Polaris admins feel

far more comfortable in that megadata silo than they do in the

sixteen tables of the PolarisTransactions database. That’s probably

because you can easily make the obvious connections between

tables in the Polaris database. Need some patron registration data?

You’ll find it in the PatronRegistration table. Oh, you need to get

the patron’s barcode? That’s just an INNER JOIN to the Patrons

table. Need to know the barcode of a certain item? Pull in the

CircItemRecords table. It all makes sense, really. CircItemRecords

has the item data, BibliographicRecords holds the bibliographic

data, and the PatronRegistration table has the patron registration

data.

Meanwhile, over in the PolarisTransactions database…

well, what in the hell is a TransactionHeader and how does it help

you get interesting data?

Before we go faffing about in the PolarisTransactions

database, maybe we should look at the important tables and get an

10

Daniel Messer

idea of the structure involved. If you feel uncomfortable

approaching PolarisTransactions and its sixteen tables, you can

take heart in the fact that, most of the time, you will likely only use

half of those tables. Here are the sixteen tables, and I’ve

highlighted the eight most important tables:

•AggregateGroupings

•Aggregates

•AggregateSpecifications

•AggregateTypes

• EmailLog

• NotificationLog

•PolSupportMainTable

•PolSupportMainTableTwo

•SA_PurgePatronIDRunCounts

• TransactionDetails

• TransactionDetailStrings

• TransactionHeaders

•TransactionRules

• TransactionSubTypeCodes

• TransactionSubTypes

• TransactionTypes

Bricks Without Clay

11

No seriously, that’s it. I’ve been pulling data from the

PolarisTransactions database for years and those eight tables are all

I use. I’m sure the other tables are useful for… something, but I

don’t know what and, frankly, I don’t care. So, since we’re only

going to worry ourselves with eight tables, we should probably

look at what they’re good for. And hey, don’t worry yourself too

much with how these tables interact with each other and the

primary Polaris database. We’ll get into all of that soon.

EmailLog – This table holds all the eReceipt email and SMS

messages. So when your patrons check out some items and Polaris

sends them an eReceipt, you’ll find that information here.

NotificationLog – If a patron gets a notification that isn’t an

eReceipt, it’s logged here. That means your overdue notices,

almost overdue/auto-renewals, hold notifications, and more. You’ll

also be able to find out if these notifications were sent through

email, phone call, text message, or print.

TransactionDetails – Almost everything that happens in Polaris

gets tagged with a TransactionHeader that lays out the basic

operation involved, where it happened, who did it, and when. The

TransactionDetails, that’s where you discover the complete story of

what happened.

TransactionDetailStrings – Sometimes, the TransactionDetails

need to precisely indicate a given data point. You’ll find those here.

TransactionHeaders – Your foundational transaction information.

Everything you need to know, from check outs to item record

creation to bibliographic record deletion and more… all of that

starts right here in this table. We’re gonna talk about this table

a lot.

12

Daniel Messer

TransactionSubTypeCodes – Expanding from the

TransactionSubTypes, you’ll find subtypes. These are more

focused aspects of what happened in a given transaction.

TransactionSubTypes – Sitting one level above the

TransactionSubTypeCodes are the TransactionSubTypes. Think of

the TransactionSubTypes as a general description within a

transaction and the TransactionSubTypeCode as the specific

description of what happened.

TransactionTypes – Apart from the TransactionHeaders, the

TransactionTypes describe the basic aspect of what happened. A

check out, a check in, the creation of a patron record, the

modification of an item record — these are all examples of a

TransactionType.

Eight tables. That’s all you need to get the entire circulation

history of a given item, or pull patron checkout statistics for a

specific ZIP code, or discover how many books were added to the

collection last year. Yeah, I get it. The PolarisTransactions table

can seem massive and intimidating.

But if you only need to concern yourself with eight tables,

you’ve literally cut the database in half. Maybe, just maybe, this

thing isn’t so monolithic after all!

Bricks Without Clay

13

Chapter Two - The View from the

Bottom: TransactionHeaders

Well, then. Now. I’ll begin at the beginnin’.￼

~Father Peter Lonergan (Ward Bond), from The Quiet Man (1952)

B

efore you fire up Azure Data Studio and start writing

queries targeting the PolarisTransactions database, you need

to accept three things:

1. Almost everything is numerical code.

2. Those numerical codes can be referenced through other

tables.

3. Most importantly, always start with the TransactionHeaders

table.

I’ve never seen this properly spelled out anywhere. If you

approach the Transactions database with the knowledge that there

is a hierarchy, and that hierarchy is the key to solving the mysteries

of those numerical codes and what they do; then you don’t have to

be Robert Langdon to pull that sweet, sweet data. Your key,

14

Daniel Messer

literally the primary key, to all of the Polaris historical data

records…

Is TransactionID.

From the look of it, the

PolarisTransactions.Polaris.TransactionHeaders table seems

simple, maybe too simple. Maybe it is, but decisions were made

long ago by people who didn’t consult us about the design of the

PolarisTransactions database. Throughout the day and into the

night, there are a lot of things that can happen in your Polaris

system. People are checking items out, then they’re checking them

in. Cataloguers are creating bibliographic and item records.

Another librarian is deleting item records because maybe y’all

don’t need that many James Patterson books after all. Your front

desk is making library cards. Patrons are logging into the PAC.

Staff are logging into Leap.

All of these things, and far more, can be found in your

TransactionHeaders table. Every check out, every item created,

every bibliographic record catalogued, every move you make,

every step you take, the TransactionsHeaders table is watching

you. Each action is separately logged and tagged with details.

(Don’t worry, we’ll get to the details, mes amies.)

If you want a full list of the transactions being logged on

your system, you’ll find that in Polaris System Administration

under System -> Database Tables -> Transaction Logging.

There are many ways you can set this up, but the right way is to

answer yes to everything. You’re not going to save room on your

PolarisTransactions database. It will grow and it will be huge. May

as well get all the functionality it provides.

Bricks Without Clay

15

Q: Which transactions would you like to log? A: Yes.

For all that it unlocks, TransactionHeaders only has seven

columns. Have a look at what they are and their data types:

TransactionID – INTEGER – The Primary Key. Your quest begins

here.

OrganizationID – INTEGER – Where did this transaction

happen? (Refers to Polaris.Polaris.Organizations.)

WorkstationID – INTEGER – Which computer handled this

transaction? (Refers to Polaris.Polaris.Workstations.)

PolarisUserID – INTEGER – Who made this transaction? (Refers

to Polaris.Polaris.PolarisUsers.)

TransactionDate – DATETIME – When did this transaction hit

the server?

16

Daniel Messer

TransactionTypeID – INTEGER – What kind of transaction was

this? (Refers to PolarisTransactions.Polaris.TransactionTypes.

More on that later.)

TranClientDate – DATETIME – What time did the transaction

occur on the workstation?

Now, normally, your TransactionDate and TranClientDate

are the same or within milliseconds of each other. You may find

some differences with asynchronous data entry, like student

imports, where there may be a difference in the time the data was

created and the time it arrives at the server. But those should be

exceptions and not rules.

PRO TIP: If you’re writing a query directed toward the

TransactionHeaders database, and you’ve got a time span, always

use TranClientDate for your time span parameters. TranClientDate

is indexed while TransactionDate isn’t, so your queries will

execute more quickly using TranClientDate.

Right, let’s take a look at some real TransactionHeaders.

Just the five things that happened most recently:

SELECT

TOP 5 *

FROM

PolarisTransactions.Polaris.TransactionHeaders

WITH (NOLOCK)

ORDER BY

TranClientDate DESC

And we get:￼

Bricks Without Clay

17

Okay, great. As you can see, each transaction has a unique

TransactionID. Some of these transactions happened at a branch

(OrganizationID = 3) and others happened at the system level

(OrganizationID = 1). If you’re wondering exactly what the

OrganizationIDs mean, you’ll find the answers in

Polaris.Polaris.Organizations. You’ll find they all happened on

WorkstationID = 2 which is the production server for this Polaris

environment and is likely the WorkstationID for your production

server too. Query Polaris.Polaris.Workstations and you’ll find the

IDs there. PolarisUserID 1? That’s PolarisExec, the system level

Polaris user that acts on behalf of the Polaris ILS itself. All of these

transactions happened the morning May 17, 2023. A quick look at

Polaris.Polaris.PolarisUsers tells me that PolarisUserID 17 is some

schmuck with a username of dmesser. Someone needs to keep an

eye on him.

Then we’ve got our first true numerical puzzle to deal with.

That dmesser dude — he did a 7201 and a 7200? What’s he up to?

PolarisExec handled two 9007s and a 6014. Awesome! Good good

good. How about that? Two 9007s with a bonus 6014! I mean,

right?! Are you seeing this?

Now, what in the hell is a 9007 and a 6014?

We’ll get to that, brick by brick.

18

Daniel Messer

Chapter Three - Just the Facts,

Ma’am: TransactionTypes

Steve McCroskey: Jacobs, I want to know absolutely everything

that’s happened up till now.

Jacobs: Well, let’s see. First the earth cooled. And then the

dinosaurs came…￼

Airplane II: The Sequel (1982)

I

n the last post, we went over TransactionHeaders and all the

numerical codes baked into a single TransactionHeader. Most

of the codes made sense, I think? The TransactionID is your

primary key and is literally the foundation of discovering the

wealth of data in your PolarisTransactions database. The

OrganizationID refers to your organizations and you can reference

that number in the Polaris.Polaris.Organizations table. The

WorkstationID can be found in Polaris.Polaris.Workstations. The

PolarisUserID is the person or system user that created the

transaction, and those numbers are references in

Polaris.Polaris.PolarisUsers. And then there were dates to tell you

when this transaction happened.

Bricks Without Clay

19

But, uh… what happened?

That’s where we encounter our first numerical mystery, the

column known as TransactionTypeID. For while we begin our

journey with TransactionID, we take our first step with

TransactionTypeID.

The TransactionTypeID refers to another table within the

PolarisTransactions database, specifically

PolarisTransactions.Polaris.TransactionTypes. We can easily see

the whole list in Azure Data Studio or SQL Server Management

Studio:

SELECT

*

FROM

PolarisTransactions.Polaris.TransactionTypes WITH

(NOLOCK)

You’ll get just over 300 transaction types back but, if you

scroll the list for just a second, you’ll notice a lot of them are

marked OBSOLETE. Now, you may be wondering why Polaris

retains these OBSOLETE transaction types in the TransactionType

table. That’s a good question and there’s a good reason for it:

Depending on the age of your system, literally how far back your

20

Daniel Messer

PolarisTransactions database stretches into the past, you may have

transactions that are now marked as OBSOLETE. But they’re still

there, and you can still reference them. You can get counts, use

them to pull data, and so on. If we remove those OBSOLETE

transaction types, concentrating only on what’s happening in a

modern Polaris system:

SELECT

*

FROM

PolarisTransactions.Polaris.TransactionTypes WITH

(NOLOCK)

WHERE

TransactionTypeDescription NOT LIKE ‘OBSOLETE%'

You’ll get 233 rows back. So, as of this writing, Polaris is

tracking 233 different kinds of transactions in the

PolarisTransactions database. It’d be a good idea to take a look at

this table and see how it’s constructed. You have five columns in

this table:

TransactionTypeID – Your primary key for this table. You’ll

notice that the table starts with a TransactionTypeID value of 1000.

And you may be wondering why it does that, which is also a good

question and there’s a good reason for it… I bet. Because I have no

idea why this table starts with 1000.

TransactionTypeDescription – This lengthy column title

describes what that TransactionTypeID actually means. You’ll

notice there’s a bit of a system here. TransactionTypeID 1000 –

1213 all deal with searches. TransactionTypeID 6000 – 6103 are

focused on patron and item circulation activity. The 3000 range

handles cataloguing. The good news is that, even if you don’t

bother to memorize all the numbers in the TransactionTypes table,

you can memorize what the range means and have a good idea

what you’re looking at when you’re looking at a bunch of rows

from the TransactionHeaders table.

Bricks Without Clay

21

Mnemonic – This describes the transaction in the client side code.

In many cases, this is how Polaris itself sees the TransactionType

while the TransactionTypeDescription is a more human readable

description of what’s going on.

SysAdmininuseTransaction – This column indicates whether or

not the TransactionType is being logged. It’s a binary bit so 0

means it’s not being logged while a 1 means that it is. Once again,

you can configure what transactions are logged through Polaris

System Administration under System -> Database Tables ->

Transaction Logging

IsImplemented – Is this TransactionType even implemented?

After all, you can have TransactionTypes in the table but Polaris

isn’t tracking them at all, and you can’t tell it to do so either.

You’re going to find a lot of 0s next to the OBSOLETE

TransactionTypes because they’re in there, but they’re no longer

implemented. Additionally, Polaris could always add a new

TransactionType in the future, but the client isn’t quite ready to

deal with it, so it could be turned off by setting this bit to 0.

Okay, great. We kind of know what we’re looking at in the

TransactionTypes table, but let’s apply that to our

TransactionHeaders, where things are actually happening. One of

the most basic questions asked of any Polaris SQL Developer is

“How many items checked out between these two dates?”

Fantastic! Let’s work with that!

If you scan your list of TransactionTypes, you’ll find that

6001 is check out. So you’ll know that every time you see

TransactionTypeID 6001 in the TransactionHeaders table, that

TransactionID corresponds to a check out. Excellent, because you

wanna count those! Let’s say you want a count of checkouts from

all of 2022. Like Lego, we combine our clauses into our SQL

query and build ourselves something like this:

22

Daniel Messer

SELECT

COUNT(*)

FROM

PolarisTransactions.Polaris.TransactionHeaders

WITH (NOLOCK)

WHERE

TransactionTypeID = 6001

AND

TranClientDate BETWEEN '2022-01-01 00:00:00.000'

AND '2022-12-31 23:59:59.999’

You could get the same kind of information for how many

items were checked in. A check in is a 6002 TransactionTypeID, so

if we change that one aspect in our query:

SELECT

COUNT(*)

FROM

PolarisTransactions.Polaris.TransactionHeaders

WITH (NOLOCK)

WHERE

TransactionTypeID = 6002

AND

TranClientDate BETWEEN '2022-01-01 00:00:00.000'

AND '2022-12-31 23:59:59.999’

I often recommend that people keep a short list of the

important, or at least the most common, TransactionTypeIDs for

their library. “But wait, Dan!” you might be asking, “How do I

know what the most common TransactionTypes are?” Hey, I’ve got

you, let’s take another look at 2022 and see what went down in

your library system, and find out what the most common things

were:

Bricks Without Clay

23

SELECT

th.TransactionTypeID AS [TransactionTypeID],

tt.TransactionTypeDescription AS [Transaction

Type Description],

COUNT(DISTINCT th.TransactionID) AS [Count]

FROM

PolarisTransactions.Polaris.TransactionHeaders th

WITH (NOLOCK)

INNER JOIN

PolarisTransactions.Polaris.TransactionTypes tt

WITH (NOLOCK)

ON (tt.TransactionTypeID = th.TransactionTypeID)

WHERE

th.TranClientDate BETWEEN '2022-01-01

00:00:00.000' AND '2022-12-31 23:59:59.999'

GROUP BY

th.TransactionTypeID,

tt.TransactionTypeDescription

ORDER BY

[Count] DESC

Looking at my example library, here are the top 20.

24

Daniel Messer

Needless to say, checking out and checking in is a popular

activity at the library, right? But every library is different, and you

may have noticed a little something at the number five position.

Holy cats! 49,017 deleted patron registrations? What happened?

Simple, they had their first inactive patron purge in a long

time. Nothing to see there, but it goes to show that what’s common

at one library may not even show up on the top 40 from another

library. Have a look at your PolarisTransactions and take a few

minutes to write down the codes you think are the most important.

Some folks keep them in a spreadsheet, others have paper lists at

their desks they can refer to. However you do it you’ll find that, as

you write more and more code, querying the PolarisTransactions

database, you’ll just naturally remember these codes anyway.

That in mind, we’ll take a look at writing some code in the

next post. Theoretical information is good, but PolarisTransactions

Bricks Without Clay

25

is a practical database, so we’ll get cooking with real, and useful,

queries!

26

Daniel Messer

￼

Chapter Four - Cookbook: Part One

Y

ou may find yourself looking at the last couple of posts

and thinking “Can I get any useful information just by

using the TransactionHeaders and TransactionTypes

tables?”

And you may find yourself looking at some of the example

queries and thinking “Well, I might be able to get some basic

circulation statistics.”

And you may find yourself in a beautiful house, with a

beautiful wife

And you may ask yourself, “Well, how did I get here?”

🎶 LETTING THE DAYS GO BY 🎶

🎶  LET THE WATER HOLD ME DO… 🎶

Wait, no… I got off track somewhere.

And yes! You can certainly get some basic circulation

statistics! Honestly, as a former Circ guy, I’m into that kind of

thing. Don’t worry about the fact that you can only count the base

number of circulations by acting upon the 6001 (Check out)

Bricks Without Clay

27

TransactionTypeID. Soon enough, we’ll build far more complex

queries using even more of the PolarisTransactions database but,

for now, one step at a time.

But you know, we can do more, and that’s the point of this

post. I want to throw a few queries at you and you’ll see how you

can pull useful data from PolarisTransactions, and you’re only

going to use the TransactionHeaders and the TransactionTypes.

(Along with a few joins to the primary Polaris database to get some

human readable information.)

The Most Recent 100 Logins

Sometimes, you may need to know if someone is logging

into Polaris at all because that’s a prerequisite for, ya know, doing

their jobs. I’ve had to deal with that before and, turns out, the staff

member wasn’t ever logging into the system. That’s weird, since

they have to do that to do anything useful.

…sigh…

Anyway, if you pull the top 100 logins, and then sort them

with a descending TranClientDate? You can find out who’s recently

logged in. And since the TransactionHeaders gives you the

workstation, user, and organization? You can get quite specific

about not only who logged in, but where.

SELECT TOP 100

pu.Name AS [Polaris Username],

o.Name AS [Library/Branch],

w.ComputerName AS [Workstation Name],

w.DisplayName AS [Workstation Display],

th.TranClientDate AS [Logon Date & Time]

FROM

PolarisTransactions.Polaris.TransactionHeaders th

WITH (NOLOCK)

28

Daniel Messer

INNER JOIN -- Bring in the organizations

Polaris.Polaris.Organizations o WITH (NOLOCK)

ON (o.OrganizationID = th.OrganizationID)

INNER JOIN -- Bring in the Polaris user info

Polaris.Polaris.PolarisUsers pu WITH (NOLOCK)

ON (pu.PolarisUserID = th.PolarisUserID)

INNER JOIN -- Bring in workstation info

Polaris.Polaris.Workstations w WITH (NOLOCK)

ON (w.WorkstationID = th.WorkstationID)

WHERE -- System logon

th.TransactionTypeID = 7200

ORDER BY

th.TranClientDate DESC

Check Outs at a Branch

Sure sure sure, circ stats are quickly available thanks to our

6001 TransactionTypeID, but hey, you can get branch specific circ

data through an OrganizationID clause. Say you’ve got a branch,

and its OrganizationID is 5. Great! We can get some year-to-date

circ stats for that branch alone!

SELECT

COUNT(DISTINCT TransactionID)

FROM

PolarisTransactions.Polaris.TransactionHeaders

WITH (NOLOCK)

WHERE -- The OrganizationID for our branch

OrganizationID = 5

AND -- Check outs

TransactionTypeID = 6001

AND

Bricks Without Clay

29

TranClientDate BETWEEN '2023-01-01 00:00:00.000'

AND '2023-12-31 23:59:59.999’

A Count of PowerPAC Keyword Searches

Librarians love stats and cats and possibly stats about cats,

but seriously, it’s interesting to know how many keyword searches

you’re seeing on your PowerPAC. TransactionTypeID 1006

specifically tracks keyword searches, but check out the rest of the

1000 range too. You can always mix and match those types for

more, or more precise, information. Here’s a query that pulls year-

to-date numbers of keyword searches.

SELECT

COUNT(DISTINCT TransactionID)

FROM

PolarisTransactions.Polaris.TransactionHeaders

WITH (NOLOCK)

WHERE -- PAC keyword search

TransactionTypeID = 1006

AND

TranClientDate BETWEEN '2023-01-01 00:00:00.000'

AND '2023-12-31 23:59:59.999’

The Busiest (or Slowest) Workstations

Where are the busiest workstations in your libraries? The

answer may surprise you, especially if you ask the question a

different way: “Which workstations have the most circulation

related activity?” You can define that several ways but, as a former

circ jerk, I’m talking about the number of check outs, check ins,

holds placed, patrons created, and patrons updated. For our

TransactionTypeIDs that means we’re concerned with:

2001 – Patron registration created

30

Daniel Messer

2002 – Patron registration modified

6001 – Check out

6002 – Check in

6005 – Hold request created

Right, so let’s also bring in the workstation display names

from Polaris.Polaris.Workstations. That’ll make reading our data a

lot easier.

SELECT

w.DisplayName AS [Workstation],

tt.TransactionTypeDescription AS [Activity],

COUNT(DISTINCT th.TransactionID) AS [Activity

Count]

FROM

PolarisTransactions.Polaris.TransactionHeaders th

WITH (NOLOCK)

INNER JOIN

PolarisTransactions.Polaris.TransactionTypes tt

WITH (NOLOCK)

ON (tt.TransactionTypeID = th.TransactionTypeID)

INNER JOIN

Polaris.Polaris.Workstations w WITH (NOLOCK)

ON (w.WorkstationID = th.WorkstationID)

WHERE

th.TransactionTypeID IN (2001, 2003, 6001, 6002,

6005)

AND

th.TranClientDate BETWEEN '2023-01-01

00:00:00.000' AND '2023-12-31 23:59:59.999'

GROUP BY

w.DisplayName,

Bricks Without Clay

31

tt.TransactionTypeDescription

ORDER BY

w.DisplayName,

tt.TransactionTypeDescription

But… but!

We can do better.

That query above, it produces some pretty interesting data

for the year-to-date activities at your workstations. But you know

what’s even better?

Totals!

We can do some math and we can rename some row

information based upon that math. So with a little CASE here and

another CASE there, we can pull in totals and save ourselves some

adding.

SELECT

CASE

WHEN w.DisplayName IS NULL THEN 'ALL

WORKSTATIONS'

ELSE w.DisplayName

END AS [Workstation],

CASE

WHEN GROUPING(tt.TransactionTypeDescription)

= 0 THEN tt.TransactionTypeDescription

WHEN GROUPING(tt.TransactionTypeDescription)

= 1 THEN 'TOTAL WORKSTATION ACTIVITY'

END AS [Activity],

COUNT(DISTINCT th.TransactionID) AS [Activity

Count]

FROM

PolarisTransactions.Polaris.TransactionHeaders th

WITH (NOLOCK)

32

Daniel Messer

INNER JOIN

PolarisTransactions.Polaris.TransactionTypes tt

WITH (NOLOCK)

ON (tt.TransactionTypeID = th.TransactionTypeID)

INNER JOIN

Polaris.Polaris.Workstations w WITH (NOLOCK)

ON (w.WorkstationID = th.WorkstationID)

WHERE

th.TransactionTypeID IN (2001, 2003, 6001, 6002,

6005)

AND

th.TranClientDate BETWEEN '2023-01-01

00:00:00.000' AND '2023-12-31 23:59:59.999'

GROUP BY

w.DisplayName,

tt.TransactionTypeDescription WITH ROLLUP

ORDER BY

w.DisplayName,

tt.TransactionTypeDescription ASC

Now that’s what I’m talkin’ about!

same as it ever was

same as it ever was

same as it ever was

same as it ever was

Bricks Without Clay

33

Chapter Five - The Devil, the Data,

and the Deep Blue Details

The devil bowed his head because he knew that he’d been beat

And he laid that golden fiddle on the ground at Johnny’s feet￼

~Charlie Daniels, “The Devil Went Down to Georgia”

More Information Than TransactionHeaders

I

n the last few posts, we started from a single TransactionID

nestled inside the expanse that is the

PolarisTransactions.Polaris.TransactionHeaders table. From

that table we’re able to get information about where a transaction

happened, who did it, and when. We’re able to bring in the

PolarisTransactions.Polaris.TransactionTypes table and discover

exactly what happened. This TransactionID was a type of 6001.

That’s a check out. This one is a 3007, someone made an item

record. Over here is a new hold request, that’s a 6005. And while

that’s good information, some of you may have picked up on a

distinct lack of specifics.

Let’s take that 6001 check out. Okay, on May 30, 2023 at

11:45:15 AM there was a 6001 transaction. Fine, but:

34

Daniel Messer

•What did they check out?

•Can we find out the name of the patron who checked it out?

•Can we find out any distinct information about that patron

or the item?

•Was the item checked out at the desk or at a self-check?

It looks like you’re missing out on a lot of details about

various transactions. These details be good for answering questions

like “When was that item last checked out, and by whom?”

Moving beyond that, you’re missing important information that

could help you figure out how the number of checkouts by material

type, or patrons within certain ZIP codes, or types of patrons

within those ZIP codes, or how many material types checked out to

specific patron codes within given ZIP codes. You’d hope that

Polaris is keep track of those details, and it is.

Welcome to PolarisTransactions.Polaris.TransactionDetails.

Devilish Details

When people talk about how inapproachable or weird or

even how scary the TransactionDetails database can be, it’s usually

because they discovered the TransactionDetails table. This table is

gigantic and oddly structured that, yeah, I can see why people

might think it’s a haunted house of long dead data. And, to be

perfectly honest with you, dear reader, TransactionDetails is quite

different from most of the other Polaris tables. Haunted? Well,

probably not, but if you happen to have an unlicensed nuclear

accelerator you’d like to bring along? Who am I to stop you?

The TransactionDetails table starts off weird and then it

gets stranger from there. One of the first things you’ll notice is

there is no primary key. Where almost every table in the Polaris

database will have a primary key to tag each and every little thing

Bricks Without Clay

35

as a separate entity, the TransactionDetails table doesn’t do that. If

you pull the most recent 25 rows from that table, you’ll have to do

it by TransactionID. That’s because the TransactionHeaders table

has a very healthy relationship with the TransactionDetails table

and that relationship is based upon the communication they have

between them. The TransactionHeaders table is vague about what’s

going on, but TransactionDetails knows the full story. And now

that I’ve tortured this metaphor long enough, here’s what you

might see:

SELECT TOP 25

*

FROM

PolarisTransactions.Polaris.TransactionDetails

WITH (NOLOCK)

ORDER BY

TransactionID DESC

36

Daniel Messer

Um… what?

That’s, uh… That’s very interesting. Yes, fascinating.

Hrmmm…

No seriously, what the hell?

Don’t worry, I know this looks bad, but think of it like

code-breaking, because you are obviously staring at codes. And

just like the AACS encryption key had to be included with every

DVD ever sold, the PolarisTransactions database includes

Bricks Without Clay

37

references and other tables to help you break these codes too! Now

you can copy all the DVDs you want!

Hold on, no… we’re talking about databases. Also, don’t

copy DVDs. It’s 2023. We can do piracy much better than that.

All right so an overview is in order. You’ve got four

columns here:

TransactionID – This column holds on to the TransactionID of a

given event in the TransactionHeaders table. Every TransactionID,

which we now know is defined by its TransactionTypeID, will

produce a set of details that are written to the TransactionDetails

table. Because various transactions have multiple sets of details,

it’s quite normal to see a TransactionID repeated multiple times

within the rows of the TransactionDetails table.

TransactionSubTypeID – Now here, this is where your details

live. When we asked questions about that 6001 transaction, we

asked about the item that was checked out — its title, material

type, and so on. We asked about the patron who checked it out —

their information, patron code, and so forth. This column calls out

all of that for you. As you’re well aware now, PolarisTransactions

is a sea of numerical codes, and these codes work together to

create structured data. There are numerical codes for

TransactionSubTypeIDs and they tag what you’re looking at.

You’ll find all of these codes, and what they mean in

PolarisTransactions.Polaris.TransactionSubTypes. There’s one

for MaterialTypeID, which you can reference by joining in your

Polaris.Polaris.MaterialTypes table. There’s another one for

PatronCodeID, which you pull in through your

Polaris.Polaris.PatronCodes table. There’s one for ItemRecordID

and PatronID, which you can send through joined

Polaris.Polaris.CircItemRecords and Polaris.Polaris.Patrons tables.

You could consider this to be the label for the data, which begs the

question “If this is the label, then where’s the actual data?” Well…

38

Daniel Messer

numValue – Here is the actual data. If I told you that the

TransactionDetails has a TransactionSubTypeID of 6 and then a

numValue next to it, that doesn’t mean much. But what if I tell you

that TransactionSubTypeID 6 means PatronID and that numValue

is the actual value of that PatronID? I hope that helps unlock and

open the method behind the madness of TransactionDetails. If I tell

you that TransactionSubTypeID 4 is the code for MaterialTypeID,

then you can bet the numValue associated with that is the

MaterialTypeID found in Polaris.Polaris.MaterialTypes. Don’t

worry, we’re going to dive deep into this topic and I think all will

become clear.

dateValue – The dateValue is there because some

TransactionSubTypeIDs aren’t necessarily set for a numerical

value, but rather for a date value. A common example of this is

TransactionSubTypeID 186, which is the Checkout Date. Those no

numerical codes to associate with a date, so you’ll find that rows

containing TransactionSubTypeID 186 have a NULL numValue,

but a DATETIME dateValue.

Get Hyped About SubTypes

We just talked a bit about the

PolarisTransactions.Polaris.TransactionSubTypes table and that’s

for good reason. This is your decoder ring that reminds you to

drink your Ovaltine. This table defines what all those numbers

mean in the TransactionSubTypeID column. This table has much in

common with our old friend TransactionTypes, so let’s peek at the

set up.

TransactionSubTypeID – Your primary key and your SubTypeID.

Each TransactionSubType has its own value and it’s completely

normal and expected that a given TransactionID in the

TransactionDetails table will have multiple

TransactionSubTypeIDs associated with it. After all, that’s how you

define the details.

Bricks Without Clay

39

TransactionSubTypeDescription – You need a definition, right?

Because the code isn’t much unless you know what it all means.

So this column actually describes what that TransactionSubTypeID

stands for.

DataType – This one is a little weird and, I admit, it’s never been a

big deal for me throughout the years. However, if you’re interested

you’ll see one of five different values here that define the data type

for the TransactionSubTypeID. B is Boolean, D is date, K is key, N

is number, and T is text.

Mnemonic – As in TransactionTypes, this column defines what the

TransactionSubType is called in the client, behind the scenes. You

can use this, if you want, but I much prefer the

TransactionSubTypeDescription for its readability.

IsImplemented – You’ll find that some SubTypes aren’t being

used anymore and those are called out as OBSOLETE in the table.

This column simply offers a 0 or a 1 to indicate if that SubTypeID

is being tracked or not.

That’s all Kool and the Gang, but how do we actually get

some details about a given transaction?

Putting It All Together

I think the best place to start is with a TransactionID tagged

with a 6001 TransactionTypeID. In other words, let’s look at a

check out.

A 6001 is a good start because there are so many interesting

details to be found in a single item checkout. It gives us a big ol’

sandbox to play in and get our heads wrapped around the pairing of

the TransactionDetails and TransactionSubTypes tables. Here in

the PolarisTransactions test kitchen, I’ve already prepared one in

40

Daniel Messer

advance with a lovely TransactionID of 6176073. Let’s pull the

details for a specific 6001, building our structure out from there:

SELECT

*

FROM

PolarisTransactions.Polaris.TransactionDetails td

WITH (NOLOCK)

WHERE

td.TransactionID = 6176073

ORDER BY

td.TransactionSubTypeID ASC

And we get something like this:￼

Bricks Without Clay

41

Wow! 19 different rows for a single checkout! But what

does this mean? What are we looking at here? What’s the

frequency, Kenneth?

Okay, remember how we were able to bring in the

PolarisTransactions.Polaris.TransactionTypes table and use that to

find out what a 6001 TransactionTypeID meant in the first place?

We can do exactly the same thing by pairing off our

TransactionDetails table with our TransactionSubTypes table. Let’s

throw a JOIN in there and see what we can find out:

SELECT

td.TransactionID,

td.TransactionSubTypeID,

tst.TransactionSubTypeDescription,

td.numValue,

td.dateValue

FROM

PolarisTransactions.Polaris.TransactionDetails td

WITH (NOLOCK)

INNER JOIN

PolarisTransactions.Polaris.TransactionSubTypes

tst WITH (NOLOCK)

ON (tst.TransactionSubTypeID =

td.TransactionSubTypeID)

WHERE

td.TransactionID = 6176073

ORDER BY

TransactionSubTypeID ASC

42

Daniel Messer

￼

Now this is what we’re talking about! We can see the

relationship between numValue and the TransactionSubTypeID. By

JOINing up the two tables, we can see that TransactionSubTypeID

4 is MaterialType and the MaterialTypeID is 18. 61 is Assigned

Collection Code and the item’s collection code is 147. 38 is the

ItemRecordID and this patron checked out ItemRecordID 211355.

All of those numbers in the numValue refer to value within their

respective tables back in the primary Polaris database. We can take

any of these values and use them to get data, statistics, forensics,

and more from the depths of the TransactionDetails. But that’s for

another chapter.

Probably the next chapter.

Bricks Without Clay

43

￼

Chapter Six - Cookbook: Part Two

T

he TransactionDetails for a given TransactionID are a

treasure trove of information for stats, troubleshooting, and

investigating. But before we explore the recesses of the

table, riding the links back and forth from TransactionSubTypes to

get references and easily readable results, there’s something I

wanted to point out. It’s one of those things that makes sense as

soon as you think about it, but if you’re new to the

PolarisTransactions database, you might not be thinking about this

at all.

Each TransactionTypeID produces different sets of

TransactionDetails.

Now, what’s that mean? Consider a 6001 TransactionID,

which is our old friend Check Out. The TransactionDetails for a

check out are going to include information about the item checked

out, its material type and collection, and when it was checked out.

You’ll also find data about the patron who checked it out, including

their PatronID, patron code, and their assigned branch.

Unsurprisingly, you’ll get a lot of the same information from a

6002, which is a Check In. However, the 6002 details will provide

information that goes with a check in. So you’ll find the loan

length (in minutes), the check in date, and the check in type.

44

Daniel Messer

Meanwhile, a 7200 TransactionID will produce one row of

information in TransactionDetails, letting you know if the person

logged into the Staff Client, Leap, the PAC, InnReach, and so on.

The point is, each TransactionTypeID produces details that

make some sense for that TransactionType. A 3008, Item Record

Created, is not going to mention anything about patrons. A 6010,

Hold Becomes Pending, will tell you nothing about supplier

records. When you start looking at the TransactionDetails for

different TransactionTypeIDs, you’re going to see different kinds

and different amounts of data. I use 6001 as a standard example

because there’s so much there! We can’t do as much with the

details from a 6010 TransactionID and we can do almost nothing

with a 7200.

The TransactionDetails for a check out (TransactionID 6001)

The TransactionDetails for a system log in (TransactionID 7200)

That in mind, let’s look at a few things we can do, putting

together some recipes to give you some ideas.

Bricks Without Clay

45

Better Transaction Details

Every single macOS device I use, I install Rocket Typist on

it. Rocket Typist is this fantastic, inexpensive bit of software that

does something wonderful. It takes small text triggers and replaces

them with bigger text substitutions. For instance, at the end of

every work email I type sig> and Rocket Typist sees that. It then

deletes sig> and replaces it with my email signature. I’ve got all

kinds of text substitutions, and I’ll drop a few links below for

programmes similar to Rocket Typist. But I told you all of that to

tell you about this.

I have a text trigger that takes btd> and replaces it with

this:

SELECT

td.TransactionID,

td.TransactionSubTypeID,

tst.TransactionSubTypeDescription,

td.numValue,

td.dateValue

FROM

PolarisTransactions.Polaris.TransactionDetails td

WITH (NOLOCK)

INNER JOIN

PolarisTransactions.Polaris.TransactionSubTypes

tst WITH (NOLOCK)

ON (tst.TransactionSubTypeID =

td.TransactionSubTypeID)

WHERE

td.TransactionID = 524053

ORDER BY

TransactionSubTypeID ASC

46

Daniel Messer

And you get something like this:￼

This code pulls the full TransactionDetails information for

any TransactionID. I even have Rocket Typist set to drop in a

pasted TransactionID because, often, my first course of action is to

obtain that TransactionID. Then I copy it, drop btd> into Azure

Data Studio and Rocket Typist replaces it with the code above. It

doesn’t matter what the TransactionTypeID is, this query gets all of

the TransactionDetails, pulls in the TransactionSubTypeID and a

readable description. The numValue corresponds to each

description and, if there’s a dateValue, that’ll be there too. Between

this query and the information contained in the TransactionHeader

itself, I can see everything that happened, where it happened, who

did it, the items and/or patrons involved, and so much more. I’ve

also got all the information I need to write a more involved query.

Maybe I need to get circulation statistics based on material

types and patron codes. That’s not something I do all that often, so

while I might remember that material types are

TransactionSubTypeID 4, I probably won’t remember that I need

to hook into TransactionSubTypeID 7 to get patron code

information. Speaking of which….

Bricks Without Clay

47

Numbers of Books Checked Out By Each Patron

Code

Keep in mind, your PatronCodeIDs will be different from

mine as will your MaterialTypeIDs. That said, we can whistle up a

quick query to get specific material type circ stats based upon the

patron codes of the patrons who checked out the items.

SELECT

pc.Description AS [Patron Code],

COUNT(DISTINCT th.TransactionID) AS [Circ Count]

FROM

PolarisTransactions.Polaris.TransactionHeaders th

WITH (NOLOCK)

INNER JOIN -- Pulls the Material Type information

PolarisTransactions.Polaris.TransactionDetails

material WITH (NOLOCK)

ON (th.TransactionID = material.TransactionID AND

material.TransactionSubTypeID = 4)

INNER JOIN -- Pulls the Patron Code information

PolarisTransactions.Polaris.TransactionDetails

patroncode WITH (NOLOCK)

ON (th.TransactionID = patroncode.TransactionID

AND patroncode.TransactionSubTypeID = 7)

INNER JOIN -- Pulls the Patron Code descriptions for

easier reading

Polaris.Polaris.PatronCodes pc WITH (NOLOCK)

ON (pc.PatronCodeID = patroncode.numValue)

WHERE -- Check outs

th.TransactionTypeID = 6001

AND -- MaterialType 18 = Books

material.numValue = 18

AND -- Au joli mois de Mai

48

Daniel Messer

th.TranClientDate BETWEEN '2023-05-01

00:00:00.000' AND '2023-05-31 23:59:59.999'

GROUP BY

pc.[Description]

ORDER BY

[Circ Count] DESC

Which gives us something like:￼

Now, I’ve got a quirk that I recommend to others. When

you’re pulling scads of TransactionSubTypeIDs from the

TransactionDetails table, it’s easy to get confused over what got

pulled from where and how it might connect to another reference.

So in the query above, I’ve pulled in the specific PatronCodeID not

from Polaris.Polaris.PatronCodes, but from

PolarisTransactions.Polaris.TransactionDetails where the

TransactionSubTypeID is 7. Here’s the INNER JOIN:

INNER JOIN -- Pulls the Patron Code information

PolarisTransactions.Polaris.TransactionDetails

patroncode WITH (NOLOCK)

ON (th.TransactionID = patroncode.TransactionID

AND patroncode.TransactionSubTypeID = 7)

For quite some time now, when pulling numValues for

TransactionSubTypes, I assign a non-abbreviated alias. I’m pulling

patron codes from PolarisTransactions.Polaris.TransactionDetails,

so I called that “patroncode.” Another join is pulling material

Bricks Without Clay

49

types, so I called it “material.” This helps future me when I’m

looking at the query three months later. I can easily find the various

joins because I took the time to give them a useful alias.

To go with that, I join in the TransactionDetails table based

upon two factors:

The TransactionID – You’ll likely build your join based on the

TransactionID since it’s the foundation of the TransactionDetails

table.

The numValue (or dateValue) of the TransactionSubTypeID –

Ultimately, this is what I want, so I build the join to get it right

away.

You can build this join is through a single reference to the

TransactionDetails, but you’ll have to call it out with a boolean

later on. For instance, this works:

INNER JOIN -- Pulls the Material Type information

PolarisTransactions.Polaris.TransactionDetails

material WITH (NOLOCK)

ON (th.TransactionID = material.TransactionID)

But you’ll need to tell SQL Server something about that

TransactionSubTypeID down in your clauses, namely the

TransactionSubTypeID you’re interested in.

AND

material.TransactionSubTypeID = 4

AND

material.numValue = 18

In my mind, that just adds lines to the code and creates

more opportunities for bugs. Not only that, it makes it harder to

join PolarisTransactions to the Polaris database. By joining in the

TransactionsDetails table based upon the TransactionID and the

TransactionSubTypeID, I can use material.numValue, or

50

Daniel Messer

patroncode.numValue, or whatever.numValue, and it cuts down on

the ambiguity.

Since these joins are referencing TransactionSubTypes,

they’ve got unabbreviated aliases. Anything else, I’ll abbreviate in

the same manner you’ll see in lots of other queries, including some

of the stuff that comes out of SimplyReports and the Supportal. So

Polaris.Polaris.PatronCodes is “pc.” Polaris.Polaris.MaterialTypes

will likely be “mat” or “mt.”

Even PolarisTransactions.Polaris.TransactionHeaders get

an abbreviated alias of th. However, months later, when I come

back to this query, I’ll know that full words (like patroncode) are

referencing TransactionSubTypeIDs in the TransactionDetails table

and abbreviations (like pc or th) are calling back to tables that

don’t reference TransactionSubTypes. You see a lot of that in:

Patron Claims History

-- Set up a variable

DECLARE @PatronID INT;

-- Drop in your PatronID below

SET @PatronID = 111;

SELECT

th.TranclientDate AS [Transaction Date],

tt.TransactionTypeDescription AS [Claim Action],

o.Name AS [Claiming Branch],

pu.Name AS [Polaris User],

p.Barcode AS [Patron Barcode],

pr.PatronFullName AS [Patron Name],

prb.Name AS [Patron Branch],

cir.ItemRecordID AS [Item Record ID],

cir.Barcode AS [Item Barcode],

iab.Name AS [Item Assigned Branch],

br.BibliographicRecordID AS [Bib Record ID],

br.BrowseTitle AS [Title],

Bricks Without Clay

51

br.BrowseAuthor AS [Author]

FROM

PolarisTransactions.Polaris.TransactionHeaders th

WITH (NOLOCK)

INNER JOIN -- Get a readable transaction type

PolarisTransactions.Polaris.TransactionTypes tt

WITH (NOLOCK)

ON (th.TransactionTypeID = tt.TransactionTypeID)

INNER JOIN -- Pulls the PatronID from

TransactionDetails

PolarisTransactions.Polaris.TransactionDetails

patron WITH (NOLOCK)

ON (th.TransactionID = patron.TransactionID AND

patron.TransactionSubTypeID = 6)

INNER JOIN -- Joins the PatronID to

PatronRegistration for further info

Polaris.Polaris.PatronRegistration pr WITH

(NOLOCK)

ON (pr.PatronID = patron.numValue)

INNER JOIN -- Brings in the Patron table for barcode

and registered branch

Polaris.Polaris.Patrons p WITH (NOLOCK)

ON (p.PatronID = pr.PatronID)

INNER JOIN -- Joins the Patron table to Organizations

to get patron registered branch

Polaris.Polaris.Organizations prb WITH (NOLOCK)

ON (prb.OrganizationID = p.OrganizationID)

INNER JOIN -- Pulls the ItemRecordID from

TransactionDetails

PolarisTransactions.Polaris.TransactionDetails

item WITH (NOLOCK)

ON (th.TransactionID = item.TransactionID AND

item.TransactionSubTypeID = 38)

LEFT JOIN -- Joins the ItemRecordID to

CircItemRecords for further info

Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)

ON (cir.ItemRecordID = item.numValue)

INNER JOIN -- Pulls the BibliographicRecordID from

TransactionDetails

52

Daniel Messer

PolarisTransactions.Polaris.TransactionDetails

bibrecord WITH (NOLOCK)

ON (th.TransactionID = bibrecord.TransactionID

AND bibrecord.TransactionSubTypeID = 36)

INNER JOIN -- Joins the BibliographicRecords table to

get title and author info

Polaris.Polaris.BibliographicRecords br WITH

(NOLOCK)

ON (br.BibliographicRecordID =

bibrecord.numValue)

INNER JOIN -- Pulls the item's assigned branch from

TransactionDetails

PolarisTransactions.Polaris.TransactionDetails

itembranch WITH (NOLOCK)

ON (th.TransactionID = itembranch.TransactionID

AND itembranch.TransactionSubTypeID = 125)

INNER JOIN -- Applies the Organizations tablet to get

a name for the item assigned branch

Polaris.Polaris.Organizations iab WITH (NOLOCK)

ON (iab.OrganizationID = itembranch.numValue)

INNER JOIN -- Joins the Organizations table to get

the branch where the transaction occurred

Polaris.Polaris.Organizations o WITH (NOLOCK)

ON (th.OrganizationID = o.OrganizationID)

INNER JOIN -- Pulls in the PolarisUsers to get a

username

Polaris.Polaris.PolarisUsers pu WITH (NOLOCK)

ON (th.PolarisUserID = pu.PolarisUserID)

WHERE -- The following IDs are for claims activity

th.TransactionTypeID IN

(6026,6027,6028,6029,6050)

AND -- Drops the variable into the query

patron.numValue = @PatronID

ORDER BY

th.TranClientDate ASC

So yeah, there are a lot of joins here. But scan down

through them and take a look at their aliases. Abbreviations like

“pr” call back to Polaris.Polaris.PatronRegistration and “br”

Bricks Without Clay

53

references Polaris.Polaris.BibliographicRecords. Those tables

come into play thanks to joins from TransactionDetails using

aliases like “patron” and ‘bibrecord’ because they’re referencing

TransactionSubTypeIDs. Sure, you don’t have to do any of this,

but I highly recommend you try it. That’ll help someone else later

on, who’s reading your query and trying to decipher what it does.

Even better, that someone could be you, so present you can do

future you a big favour by styling those TransactionSubType

references for easier reading.

Good and bad, I define these terms

Quite clear, no doubt, somehow

Ah, but I was so much older then

I’m younger than that now

~Bob Dylan, “My Back Pages”

Text expansion software

Seriously, save yourself some time!

aText – For macOS.

Rocket Typist — For macOS, and available through Setapp.

PhraseExpress – For Windows.

Text Expander– For macOS and Windows.

espanso – For Linux.

54

Daniel Messer

Chapter Seven - The Codes Under the

Codes Beneath the Type

I never felt this way before. We are machines, stamped out like

bottle caps. It’s an illusion that I – I personally – really exist; I’m

just representative of a type.

~Rachael Rosen, from Do Androids Dream of Electric Sheep? by￼

Philip K. Dick

Y

ou know, sometimes, the PolarisTransactions database

feels like a special type of hell. Specifically the type of hell

envisioned by Dante Alighieri in The Divine Comedy.

There are levels and layers and levels beneath those, and as soon as

you think you’re done with something you find yourself walking

barefoot across a frozen wasteland.

I… sense I have lost control of my metaphor.

That aside, let me be your Virgil as we venture deeper into

the database, moving below the TransactionSubTypes into the level

of TransactionSubTypeCodes. Yes, beloved, there are more codes

Bricks Without Clay

55

to be found but, as always, we are provided a key. The trick is,

knowing how the key works, and which doors it opens.

With certain TransactionTypes, you’ll find a

TransactionSubTypeID that refers to a code not found in the

primary Polaris database, Around here, we love checking things

out because the 6001 TransactionTypeID offers so many

opportunities to learn how the PolarisTransactions database works.

So let’s take a look at the TransactionDetails for an item checkout:

Now, if you have a look at line 10 there, you’ll see a

TransactionSubTypeID of 145 that’s described as Checkout Type.

The numValue associated with that is 51. Take note, that’s how

TransactionSubTypeCodes work, through pairing. You need both

of those codes to discover the meaning behind a Checkout Type.

But, as Miles Davis once asked, “So what?”

Well, this Checkout Type opens up another statistical point

that you can track in your SQL and in your reports. The Checkout

Type identifies how the item was checked out. And, to make sense

of that numValue, we need to reference the

56

Daniel Messer

PolarisTransactions.Polaris.TransactionSubTypeCodes table.

As of this writing, the TransactionSubTypeCodes table is small,

with only 81 rows. That’s a bit refreshing, isn’t it? Even better,

there are only three columns:

TransactionSubTypeID – The TransactionSubTypeID referenced

in your TransactionDetails. You will find that there are multiple

entries for the same TransactionSubTypeID because this table has

no primary key.

TransactionSubTypeCode – This references the numValue found

in your TransactionDetails, In our example above, on line 10 we’re

looking at a TransactionSubTypeID of 145 and a numValue of 51. I

guarantee you, you will find a row in the

TransactionSubTypeCodes that matches those two criteria. Once

you have that row, you’ll learn what that pairing means through

the…

TransactionSubTypeCodeDesc – The human readable definition

of the paring.

Right, we know our TransactionSubTypeID (145) and we

know the TransactionSubTypeCode (51). We can query the

TransactionSubTypeCodes table:

SELECT

*

FROM

PolarisTransactions.Polaris.TransactionSubTypeCodes

WITH (NOLOCK)

WHERE -- Checkout Type from TransactionDetails

TransactionSubTypeID = 145

AND -- The numValue associated with it

TransactionSubTypeCode = 51

And SQL Server returns the following:

Bricks Without Clay

57

￼

I can assure you, the checkout in question was something I

set up specifically to illustrate this example. I checked out an item

to myself using Polaris Leap and now you can see how that can be

tracked through PolarisTransactions. Maybe you need to find out

how well the staff are transitioning to Leap? You can leverage the

TransactionSubTypeCodes table to find out how many items are

checked out with Leap versus the Staff Client. Oh, by the way,

there are more than a couple of Checkout Types:￼

Within the last few weeks of writing this, I was asked for a

report on the number of checkouts occurring at a self-check

machine. I can get my hooks into those 6001 TransactionTypes,

58

Daniel Messer

grab the 145 TransactionSubTypeID, and use that to find

numValues of 12, 22, and 24. (I wrote a very thorough query.) If

you’re interested, here’s the code:

DECLARE @StartDate DATETIME;

DECLARE @EndDate DATETIME;

-- First day of the previous month

set @StartDate =

dateadd(mm,datediff(m,0,getdate())-1,0);

-- Last day of the previous month

set @EndDate = dateadd(s,-1,dateadd(mm,

datediff(m,0,getdate()),0));

-- Create a table to not only hold the data but

provide 0 count outputs as needed.

CREATE TABLE #TempSelfCheckStats

(

WorkstationID INT,

Name NVARCHAR(250),

DisplayName NVARCHAR(100),

SelfCheckoutCirculation INT

);

-- This table will gather the actual stats and then

use them to update the table above.

CREATE TABLE #TempPullSelfCheckStats

(

WorkstationID INT,

Name NVARCHAR(250),

DisplayName NVARCHAR(100),

SelfCheckoutCirculation INT

);

/* Populate #TempSelfCheckStats with all self-check

workstations and set their counts to zero. This pulls

in all branches

Bricks Without Clay

59

and show a zero count even if they did no self-check

activity at all for the time period. */

INSERT INTO #TempSelfCheckStats

SELECT

w.WorkstationID,

o.Name,

w.DisplayName,

0 AS [SelfCheckoutCirculation]

FROM

Polaris.Polaris.Workstations w WITH (NOLOCK)

INNER JOIN

Polaris.Polaris.Organizations o WITH (NOLOCK)

ON (o.OrganizationID = w.OrganizationID)

WHERE

w.DisplayName LIKE '%-SIP'

/* Populate #TempPullSelfCheckStats with actual self-

check circ counts. These will be used to update the

#TempSelfCheckStats table. */

INSERT INTO #TempPullSelfCheckStats

SELECT

th.WorkstationID,

o.Name,

w.DisplayName,

COUNT(th.TransactionID) AS

[SelfCheckoutCirculation]

FROM

PolarisTransactions.Polaris.TransactionHeaders th

WITH (NOLOCK)

INNER JOIN

Polaris.Polaris.TransactionDetails td WITH

(NOLOCK)

ON (th.TransactionID = td.TransactionID)

INNER JOIN

60

Daniel Messer

PolarisTransactions.Polaris.TransactionSubTypeCodes

tstc WITH (NOLOCK)

ON (tstc.TransactionSubTypeCode = td.numValue and

td.TransactionSubTypeID = 145)

INNER JOIN

Polaris.Polaris.Workstations w WITH (NOLOCK)

ON (w.WorkstationID = th.WorkstationID)

INNER JOIN

Polaris.Polaris.Organizations o WITH (NOLOCK)

ON (o.OrganizationID = w.OrganizationID)

WHERE

th.TransactionTypeID = 6001

AND

th.WorkstationID IN (

SELECT WorkstationID

FROM Polaris.Polaris.Workstations WITH

(NOLOCK)

WHERE DisplayName LIKE '%-SIP')

AND -- All the self-checkout TransactionSubTypeCodes

tstc.TransactionSubTypeCode IN (12,22,24)

AND

th.TranClientDate BETWEEN @StartDate AND @EndDate

GROUP BY

th.WorkstationID,

o.Name,

w.DisplayName

ORDER BY

w.DisplayName

/* Use data from #TempSelfCheckStats to update the

data in #TempSelfCheckOrgs.*/

UPDATE

#TempSelfCheckStats

SET

#TempSelfCheckStats.SelfCheckoutCirculation =

#TempPullSelfCheckStats.SelfCheckoutCirculation

FROM

#TempPullSelfCheckStats

Bricks Without Clay

61

WHERE

#TempSelfCheckStats.WorkstationID =

#TempPullSelfCheckStats.WorkstationID

/* Roll out the requested data */

SELECT

Name,

SelfCheckoutCirculation

FROM

#TempSelfCheckStats

ORDER BY

Name ASC

-- Tidy up

DROP TABLE #TempSelfCheckStats;

DROP TABLE #TempPullSelfCheckStats;

Now, you might find yourself wondering about the other

kinds of TransactionSubTypeCodes may be found in

PolarisTransactions.Polaris.TransactionSubTypeCodes. Or you

may find yourself wondering about Spider-Punk. I don’t know

about you, but that happens to me all the time. I frequently bounce

between the two subjects in my mind. As for those

TransactionSubTypeCodes, we can find out what’s tracked and

referenced pretty easily.

SELECT

tstc.TransactionSubTypeID,

tst.TransactionSubTypeDescription,

tstc.TransactionSubTypeCode,

tstc.TransactionSubTypeCodeDesc

FROM

PolarisTransactions.Polaris.TransactionSubTypeCodes

tstc WITH (NOLOCK)

INNER JOIN

PolarisTransactions.Polaris.TransactionSubTypes

tst WITH (NOLOCK)

ON (tst.TransactionSubTypeID =

tstc.TransactionSubTypeID)

62

Daniel Messer

￼

Scanning the results, you’ll see that there are

TransactionSubTypeCodes for:

•Invoice payment method type

•Renewal

•Checkin Type

•Checkout Type

•Registration Type

•Payment Subsystem Type

•Holds Denial Source

•eContent Type

For a bit of variety, let’s look at a patron registration, which

carries a TransactionTypeID of 2001. The TransactionDetails for a

patron registration will look something like this:

Cool, so we see that this patron has a Registration Type

(TransactionSubTypeID 188) of 22. If you check the

TransactionSubTypeCodes table, you’ll find that pairing indicates

the patron was registered through Polaris Leap.

And with this, we near the end of our journey through the

Transactions part of PolarisTransactions, but… we’re not there yet.

One more level, one more layer, with some ways to go even

beyond that.

Bricks Without Clay

63

Chapter Eight - String Theory

You promised the world

and brought me it hanging from a string

~CHVRCHES, How Not to Drown￼

An Underlying Problem

I

f you look really hard at the TransactionDetails table, well…

you’ll probably fall asleep. But if you look at the structure, you

may notice a problem.

There are four columns, three of which are designated as

integer data types and the fourth is a datetime. That means the

TransactionDetails table deals with numbers and dates and that, my

friends, is it. So if you’re going to go looking for titles, barcodes,

text, or anything that’s not a number or a date, you’re going to

have a quick search and a bad time. They’re not there.

Right?

Well, no. There’s yet another table squirreling away a

cache of information and data. But like all things in the

PolarisTransactions database, you need to know that it’s there, you

64

Daniel Messer

need to know what it does, and you need to know how it works.

And here, we return to the PolarisTransactions modus operandi of

referring to different tables via a numerical code. Björk reminded

us that All is Full of Love, but when it comes the

PolarisTransactions database, all is full of coded references.

Strings and Sealing Wax and Other Fancy Stuff

So, did you know that you can see what your patrons are

searching for on the PAC? I know, I know… that sounds like

something Amazon would do, and it is. But you can do interesting

things with such data and, as it happens, search terms are

anonymous. Polaris can track patron PAC searches, but it doesn’t

track who searched for what, even if the patron is logged into the

PowerPAC. If you’re up for a little data mining, you can take a

look at the 1006 TransactionTypeID (PAC Search Keyword) and

see if there are any trends.

But there’s a problem. Here’s a complete

TransactionDetails list for a 1006 TransactionTypeID:

Yeah, it’s a little barren isn’t it? And what is it, exactly, that

this patron searched for? Certainly they didn’t search for 2491607.

No, they didn’t. That’s your code, that’s your key to the

TransactionDetailStrings table.

The TransactionDetailStrings table is one of the simplest

tables in PolarisTransactions. There are only two columns:

TransactonStringID – Your primary key and the reference back to

TransactionDetails.

Bricks Without Clay

65

TransactionString – The text value of the referenced data.

That TransactionString column is your payload. That’s

where you’ll find the information you’re seeking when there’s

more information to be had from TransactionDetails, but the

information isn’t a date or a number. So hey, beloved, a quick

question for you? Do you like JOINS? Because if you do, I have

some good news: We’re going to need three of them:

SELECT

th.TransactionID,

td.TransactionSubtypeID,

tst.TransactionSubTypeDescription,

tds.TransactionString

FROM

PolarisTransactions.Polaris.TransactionHeaders th

WITH (NOLOCK)

INNER JOIN -- References the SearchString

TransactionSubTypeID

PolarisTransactions.Polaris.TransactionDetails td

WITH (NOLOCK)

ON (th.TransactionID = td.TransactionID and

td.TransactionSubTypeID = 23)

INNER JOIN

PolarisTransactions.Polaris.TransactionSubTypes

tst WITH (NOLOCK)

ON (tst.TransactionSubTypeID =

td.TransactionSubTypeID)

INNER JOIN -- Cross references the numValue

associated with TransactionSubtypeID 23

PolarisTransactions.Polaris.TransactionDetailStrings

tds WITH (NOLOCK)

ON (tds.TransactionStringID = td.numValue)

WHERE -- The TransactionID we're interested in

th.TransactionID = 26640105

66

Daniel Messer

And, when we run that query, we can see that the patron searched

for:￼

Turns out, the patron is looking for Helen Sweetstory’s

most controversial book. Just the kind of thing you need to know

for Banned Books Week!

We can do more with TransactionDetailStrings. Say we’ve

got a situation where someone accidentally deleted an item record.

It happens, even in the best of libraries. We need to find out the

barcode connected to that deleted item but, once again,

TransactionDetails isn’t going to tell you that. It’s going to point to

the information instead. A typical Item Record Deleted transaction,

which carries a 3007 TransactionTypeID, looks like this:￼

Take my word for it when I tell you that the item’s barcode

was not 2491042. That’s our pointer to the

TransactionDetailStrings table. So, let’s go get the barcode!

Bricks Without Clay

67

SELECT

th.TransactionID,

td.TransactionSubtypeID,

tst.TransactionSubTypeDescription,

tds.TransactionString

FROM

PolarisTransactions.Polaris.TransactionHeaders th

WITH (NOLOCK)

INNER JOIN -- References the Barcode

TransactionSubTypeID

PolarisTransactions.Polaris.TransactionDetails td

WITH (NOLOCK)

ON (th.TransactionID = td.TransactionID and

td.TransactionSubTypeID = 219)

INNER JOIN

PolarisTransactions.Polaris.TransactionSubTypes

tst WITH (NOLOCK)

ON (tst.TransactionSubTypeID =

td.TransactionSubTypeID)

INNER JOIN -- Cross references the numValue

associated with TransactionSubtypeID 219

PolarisTransactions.Polaris.TransactionDetailStrings

tds WITH (NOLOCK)

ON (tds.TransactionStringID = td.numValue)

WHERE -- The TransactionID we're interested in

th.TransactionID = 26635176

Which returns:￼

Ah ha! This was an eContent item! The real barcode in

question is econtent607337. Okay, that’ll help us track down other

information about the item!

68

Daniel Messer

And speaking of eContent, let’s take a look at a canceled

eContent hold, which is a 6102 TransactionTypeID. With a little bit

of digging, we can pull the title of the item canceled out of the

PolarisTransactions database. First, we need to figure out which

TransactionSubTypeID is referenced by TransactionDetails. Here’s

a standard 6102 TransactionDetails list:￼

Now, some readers might want to point out that you could

get the title information by joining in the BibliographicRecords

table from the primary Polaris database. After all, right there, in

TransactionSubTypeID 36, you’ve got the BibliographicRecordID.

And, you know what? You’re right, you can!

Unless that bibliographic record has been deleted. That is

going to be a problem.

So, in this case, we need to work off

TransactionSubTypeID 49, which calls out the Title. You’re going

to notice that the code looks very similar to the last couple of

queries:

SELECT

th.TransactionID,

td.TransactionSubtypeID,

tst.TransactionSubTypeDescription,

tds.TransactionString

FROM

PolarisTransactions.Polaris.TransactionHeaders th

WITH (NOLOCK)

Bricks Without Clay

69

INNER JOIN -- References the Title

TransactionSubTypeID

PolarisTransactions.Polaris.TransactionDetails td

WITH (NOLOCK)

ON (th.TransactionID = td.TransactionID and

td.TransactionSubTypeID = 49)

INNER JOIN

PolarisTransactions.Polaris.TransactionSubTypes

tst WITH (NOLOCK)

ON (tst.TransactionSubTypeID =

td.TransactionSubTypeID)

INNER JOIN -- Cross references the numValue

associated with TransactionSubtypeID 219

PolarisTransactions.Polaris.TransactionDetailStrings

tds WITH (NOLOCK)

ON (tds.TransactionStringID = td.numValue)

WHERE -- The TransactionID we're interested in

th.TransactionID = 26628783

And we find out that the title in question was Throne of

Glass.￼

Trending Searches on the PowerPAC

The basics established, I smell another Cookbook chapter

coming on, but let me leave you with something useful, a little data

mining query that’ll provide trends and information on what

patrons are looking for when they’re searching your PAC. Not only

can you spot trends, but you can see if there’s something that your

library needs to add to the collection. It’s a look into the zeitgeist

of your patrons’ interests, and honestly, it’s kinda fun to see what’s

going on out there. This will combine like searches into a single

70

Daniel Messer

row with a counter, so if there have been 63 searches for Sandra

Brown, they’ll all come up on one row with the count. Check it out

on your own Polaris system, you may be surprised by what comes

up!

-- Pull subset of data and acting on that instead of

the entire database

CREATE TABLE #TempPACSearches (

TransactionID INT

);

-- Populate the table with a list of PAC searches

from a given time

INSERT INTO #TempPACSearches

SELECT

TransactionID

FROM

PolarisTransactions.Polaris.TransactionHeaders

WITH (NOLOCK)

WHERE -- PAC Keyword Searches

TransactionTypeID = 1006

AND -- Adjust dates as needed

TranClientDate BETWEEN '2023-06-01 00:00:00.000'

AND '2023-06-30 23:59:59.999';

-- Get the search strings

SELECT

COUNT(th.TransactionID) AS [Search Count],

tds.TransactionString AS [Keyword Search]

FROM

PolarisTransactions.Polaris.TransactionHeaders th

WITH (NOLOCK)

INNER JOIN -- References the SearchString

TransactionSubTypeiD

PolarisTransactions.Polaris.TransactionDetails

search WITH (NOLOCK)

ON (th.TransactionID = search.TransactionID AND

search.TransactionSubTypeID = 23)

Bricks Without Clay

71

INNER JOIN

PolarisTransactions.Polaris.TransactionDetailStrings

tds WITH (NOLOCK)

ON (tds.TransactionStringID = search.numValue)

WHERE

th.TransactionID IN (SELECT TransactionID FROM

#TempPACSearches)

GROUP BY

tds.TransactionString

ORDER BY

COUNT(th.TransactionID) DESC;

-- Tidy up

DROP TABLE #TempPACSearches;

72

Daniel Messer

Chapter Nine - Cookbook: Part Three

Supergirl: How did… What, do you go through my trash?

The Question: Please. I go through everyone’s trash.￼

Justice League Unlimited. S01E06 – Fearful Symmetry

W

hile it’s all well and good to know that you can find

information in the TransactionDetailStrings table, it’s

more interesting to ask “What can I do with it?”. Well,

if you’re interested in more than the numerical and integer based

data that goes into the TransactionDetailStrings table, you’ll want

to find out what’s in there that isn’t based on integers. Thankfully,

there’s a fairly short query that does this for you:

SELECT

*

FROM

PolarisTransactions.Polaris.TransactionSubTypes

WITH (NOLOCK)

WHERE -- Filter out what isn't used

TransactionSubTypeDescription NOT LIKE

'OBSOLETE%'

AND -- Show me TransactionSubTypes that are tracking

text based values

DataType = ’T'

This pulls a list of TransactionSubTypes that are tracking

text based data. You can hook into these TransactionSubTypeIDs

Bricks Without Clay

73

and apply their numValues to the TransactionDetailStrings tables

through the magic of JOINs. As I’m writing this, at the beginning

of August 2023, there are 49 TransactionSubTypes set to track text

but one of them, #68 – Chronology, isn’t implemented. I cannot

think what a nightmare of code it will be to get that kind of

information out of a MARC record and into TransactionDetails.

Thank you no, mes amies, I’m sticking to PHP, Python, and SQL.

Okay, great, we have our list. So let’s bake a cake, which is

a weird way to say “write some queries” but I’m sticking with this

cookbook metaphor for all it’s worth.

Who Overrode Who, and What? (Asked the Akond of

Swat)

If you have no idea what that header means, you are one of

today’s lucky 10,000. Allow me the pleasure of introducing you to

the magnificent voice of Ken Nordine. Go have a listen to The

Akond of Swat from his 2001 album A Transparent Mask.

If you dig around in the TransactionTypes table, you’ll

come across an interesting TransactionTypeID: 9001 – Supervisor

override transactions. Now, if we look at the TransactionDetails

for a 9001 TransactionType, you’ll find there are only two rows:

Well this is interesting, because it lets us write a query that

tells us who’s overriding permissions in Polaris. Not only that, if

we work with the TransactionHeader information, we can find out

who was trying to do something, what they were trying to do, who

allowed the override, the location and workstation where this

occurred, and when. This kind of thing comes in handy when

74

Daniel Messer

you’re auditing processes and workflows. I hope you like INNER

JOIN, beloved, because we’re going to see a lot of them today.

SELECT

supervisor.Name AS [Overriding Supervisor],

staffmember.Name AS [Staff Member],

o.Name AS [Library/Branch],

w.DisplayName AS [Workstation],

permission.TransactionString AS [Overrode

Permission],

th.TranClientDate AS [Date]

FROM

PolarisTransactions.Polaris.TransactionHeaders th

WITH (NOLOCK)

INNER JOIN -- Get the userid of the overriding

supervisor

PolarisTransactions.Polaris.TransactionDetails

overuser WITH (NOLOCK)

ON (th.TransactionID = overuser.TransactionID AND

overuser.TransactionSubTypeID = 190)

INNER JOIN -- Get the TransactionStringID for the

overrode privilege

PolarisTransactions.Polaris.TransactionDetails

overprivilege WITH (NOLOCK)

ON (th.TransactionID =

overprivilege.TransactionID AND

overprivilege.TransactionSubTypeID = 191)

INNER JOIN -- Get the description of the overrode

permission

PolarisTransactions.Polaris.TransactionDetailStrings

permission WITH (NOLOCK)

ON (overprivilege.numValue =

permission.TransactionStringID)

INNER JOIN -- Get the username of the overriding

supervisor

Polaris.Polaris.PolarisUsers supervisor WITH

(NOLOCK)

ON (supervisor.PolarisUserID = overuser.numValue)

INNER JOIN -- Get the username of the staff member

Bricks Without Clay

75

Polaris.Polaris.PolarisUsers staffmember WITH

(NOLOCK)

ON (staffmember.PolarisUserID = th.PolarisUserID)

INNER JOIN -- Get the library name

Polaris.Polaris.Organizations o WITH (NOLOCK)

ON (o.OrganizationID = th.OrganizationID)

INNER JOIN -- Get the workstation info

Polaris.Polaris.Workstations w WITH (NOLOCK)

ON (w.WorkstationID = th.WorkstationID)

WHERE -- Supervisor override transactions

th.TransactionTypeID = 9001

AND

th.TranClientDate BETWEEN '2023-01-01

00:00:00.000' AND '2023-12-31 23:59:59.999'

ORDER BY

th.TranClientDate DESC

Run that, and you’ll get results like this. I’m going to redact

a few usernames just for security, but you can get the feel for what

you’ll see if you run this on your own systems.￼

Record Set Activity

Sadly, Polaris doesn’t offer a great method of tracking

exactly which item, bibliographic, patron, and authority records

have been added to a record set. But that doesn’t mean tracking

76

Daniel Messer

overall record set activity is a useless gesture. If you happen to

work in a large library or consortium, and you’re using

SimplyReports, and a user wants to use the list of record sets to

generate a report….

And you have 314,159 record sets in the system?

Uhhh, you might wanna do some clean up. Those are going

to take some time to load into the SimplyReports UI so maybe we

can start by looking at which record sets are getting used, and

what’s happening with them. It’s extremely helpful if you know the

name of the record set because, while a RecordSetID can get you

specifics, it’s usually a lot easier to remember “January Discards”

than “RecordSetID 271828.” Record Set names are stored in the

TransactionDetailStrings, and we can hook into that along with a

few tables in the primary Polaris database to get a view of what’s

going on with our sets.

SELECT

pu.Name AS [Staff Member],

tt.TransactionTypeDescription AS [Action],

th.TranClientDate AS [Action Date],

recordsetid.numValue AS [Record Set ID],

setname.TransactionString AS [Record Set Name],

ot.[Description] AS [Record Set Type],

setowner.Name AS [Record Set Owner]

FROM

PolarisTransactions.Polaris.TransactionHeaders th

WITH (NOLOCK)

INNER JOIN -- Get the RecordSetID

PolarisTransactions.Polaris.TransactionDetails

recordsetid WITH (NOLOCK)

ON (th.TransactionID = recordsetid.TransactionID

AND recordsetid.TransactionSubTypeID = 168)

INNER JOIN -- Get the record set type

PolarisTransactions.Polaris.TransactionDetails

recordsettype WITH (NOLOCK)

Bricks Without Clay

77

ON (th.TransactionID =

recordsettype.TransactionID AND

recordsettype.TransactionSubTypeID = 50)

INNER JOIN -- Get the record set's owner

PolarisTransactions.Polaris.TransactionDetails

recordsetowner WITH (NOLOCK)

ON (th.TransactionID =

recordsetowner.TransactionID AND

recordsetowner.TransactionSubTypeID = 192)

INNER JOIN -- Get the TransactionStringID for the

record set's name

PolarisTransactions.Polaris.TransactionDetails

recordsetname WITH (NOLOCK)

ON (th.TransactionID =

recordsetname.TransactionID AND

recordsetname.TransactionSubTypeID = 193)

INNER JOIN -- Get the record set's name from

TransactionDetailStrings

PolarisTransactions.Polaris.TransactionDetailStrings

setname WITH (NOLOCK)

ON (recordsetname.numValue =

setname.TransactionStringID)

INNER JOIN -- Get the Polaris username who acted on

the record set

Polaris.Polaris.PolarisUsers pu WITH (NOLOCK)

ON (pu.PolarisUserID = th.PolarisUserID)

INNER JOIN -- Get the Polaris username of the record

set owner

Polaris.Polaris.PolarisUsers setowner WITH

(NOLOCK)

ON (setowner.PolarisUserID =

recordsetowner.numValue)

INNER JOIN -- Get a description of the record set

action

Polaris.Polaris.TransactionTypes tt WITH (NOLOCK)

ON (th.TransactionTypeID = tt.TransactionTypeID)

INNER JOIN -- Get the record set type

Polaris.Polaris.ObjectTypes ot WITH (NOLOCK)

ON (ot.ObjectTypeID = recordsettype.numValue)

78

Daniel Messer

WHERE -- Transaction Types related to record set

activity

th.TransactionTypeID IN

(9002,9003,9004,9011,9012)

AND -- Set as needed - this checks between today and

30 days ago.

th.TranClientDate BETWEEN DATEADD(DAY, -30,

GETDATE()) AND GETDATE()

ORDER BY

th.TranClientDate DESC

And we’ll get something back that looks like:￼

We can see the staff member who did something to the

record set, what they did and when. Just to get some extra info,

why not pull in the type of records in the records found in the set

along with the owner?

Find Deleted Bib Records by Author

Hey, do you know how many bibliographic record sets

have been deleted for James Patterson this year? Maybe it’s time

we ordered some more James Patterson materials. I dunno, man.

We might be running out, so we better check. The

TransactionDetails for a 3001 TransactionTypeID (Bibliographic

record deleted) includes multiple TransactionSubTypes linked to

the TransactionDetailStrings table. Because of that, you can

Bricks Without Clay

79

actually search by author for certain TransactionTypes and the

3001 is one of those.

In this case, we’re going to find out how many James

Patterson bibs were deleted this year. Even better, 3001

TransactionTypes include the title and ISBN within the

TransactionDetails. We can pick up on those TransactionSubTypes

and pull them from TransactionDetailStrings too.

SELECT

th.TranClientDate AS [Deleted Date],

bibrecordid.numValue AS [Bib ID],

title.TransactionString AS [Title],

author.TransactionString AS [Author],

isbn.TransactionString AS [ISBN]

FROM

PolarisTransactions.Polaris.TransactionHeaders th

WITH (NOLOCK)

INNER JOIN -- Get the BibliographicRecordID

PolarisTransactions.Polaris.TransactionDetails

bibrecordid WITH (NOLOCK)

ON (th.TransactionID = bibrecordid.TransactionID

AND bibrecordid.TransactionSubTypeID = 36)

INNER JOIN -- Get the value of the

TransactionStringID for the Title

PolarisTransactions.Polaris.TransactionDetails

bibtitle WITH (NOLOCK)

ON (th.TransactionID = bibtitle.TransactionID AND

bibtitle.TransactionSubTypeID = 49)

INNER JOIN -- Get the value of the

TransactionStringID for the Author

PolarisTransactions.Polaris.TransactionDetails

bibauthor WITH (NOLOCK)

ON (th.TransactionID = bibauthor.TransactionID

AND bibauthor.TransactionSubTypeID = 48)

INNER JOIN -- Get the value of the

TransactionStringID for the ISBN

PolarisTransactions.Polaris.TransactionDetails

bibisbn WITH (NOLOCK)

80

Daniel Messer

ON (th.TransactionID = bibisbn.TransactionID AND

bibisbn.TransactionSubTypeID = 45)

INNER JOIN -- Get the title from

TransactionDetailStrings

PolarisTransactions.Polaris.TransactionDetailStrings

title WITH (NOLOCK)

ON (title.TransactionStringID =

bibtitle.numValue)

INNER JOIN -- Get the author from

TransactionDetailStrings

PolarisTransactions.Polaris.TransactionDetailStrings

author WITH (NOLOCK)

ON (author.TransactionStringID =

bibauthor.numValue)

INNER JOIN -- Get the ISBN from

TransactionDetailStrings

PolarisTransactions.Polaris.TransactionDetailStrings

isbn WITH (NOLOCK)

ON (isbn.TransactionStringID = bibisbn.numValue)

WHERE -- Bibliographic record deleted

th.TransactionTypeID = 3001

AND -- Put your author search below

author.TransactionString LIKE 'Patterson, James%'

AND

th.TranClientDate BETWEEN '2023-01-01

00:00:00.000' AND '2023-12-31 23:59:59.999'

ORDER BY

th.TranClientDate DESC,

author.TransactionString

Bricks Without Clay

81

And hey, looks like we weeded some records this year!

Time to buy more Patterson!￼

82

Daniel Messer

Chapter Ten - Polaris Calling

It’s log, it’s log,

It’s better than bad, it’s good!￼

~The Log Song – Ren & Stimpy

T

he only way out is through but, beloved, we’re almost

through! Now that you know how the various tables in the

PolarisTransactions database work together, you’ve

unlocked a new realm of knowledge where you can pull original

and useful data from your Polaris system! You’ve solved the

secrets of the puzzle box and the Cenobites are already on their

way!

Wait… no. Wrong metaphor.

But we’re not quite done with the TransactionsDatabase

because there are two more tables to discover! As it happens,

they’re incredibly useful and both of them serve the same purpose:

tracking patron notification history. Let’s have a scenario, shall

we?

A patron rings the library and they’re concerned because

they’ve haven’t gotten a phone call to let them know that their

Bricks Without Clay

83

holds are ready for pickup. However, they checked their account

on the library’s website and, lo and behold, their items are ready.

So… what happened?

Well, let’s go ask the NotificationLog table and see what’s

going on.

PolarisTransactions.Polaris.NotificationLog is exactly what

you might think it is, a log of notifications sent to the patrons. It

also has the largest number of columns of all the tables in the

database:

PatronID – The patron’s unique identification number.

NotificationDateTime – The date and time the notification went

out.

NotificationTypeID – The type of notification that went out.

References Polaris.Polaris.NotificationTypes.

DeliveryOptionID – The method of delivery, phone, email, etc.

References Polaris.Polaris.DeliveryOptions.

DeliveryString – This specifies the email or address the

notification went out to. If the notification was print, this column

will show NULL.

OverduesCount – This is the number of items in the notification

receiving their first overdue notice.

HoldsCount – The number of held items included within the

notice.

CancelsCount – The number of canceled items included in the

notice.

84

Daniel Messer

RecallsCount – The number of recalled items included in the

notice

NotificationStatusID – Was the notification successful or not?

Details – Any details about the notification. For telephony, this

column will hold your CPA Results.

RoutingsCount – The number of routed items in the notification.

ReportingOrgID – The OrganizationID of the library/branch

sending the notice.

PatronBarcode – The patron’s barcode at the time the notice was

sent.

Reported – If you’re set to report notification information to an

internal address, this column indicates whether or not this

notification as been included on such a report.

Overdues2ndCount -This is the number of items in the

notification receiving their second overdue notice.

Overdues3rdCount – This is the number of items in the

notification receiving their third overdue notice.

BillsCount – The number of billed items in the notification.

LanguageID – The ID of the notification language. This

references AdminLanguageID in Polaris.Polaris.Languages.

CarrierName – If it’s an SMS notification, this will be the carrier

involved.

ManualBillsCount – The number of manual bills in the

notification.

Bricks Without Clay

85

NotificationLogID – For some reason, the primary key for this

table is the final column. There’s nothing wrong with that, but it’s

kind of weird, right?

Like most tables in the PolarisTransactions database, it’s a

collection of numerical codes that refer to other tables. However,

unlike most of the tables in the PolarisTransactions database, most

of those codes refer back to the primary Polaris database. For

instance:

PatronID – Polaris.Polaris.Patrons

NotificationTypeID – Polaris.Polaris.NotificationTypes

DeliveryOptionID – Polaris.Polaris.DeliveryOptions

NotificationStatusID – Polaris.Polaris.NotificationStatuses

ReportingOrgID – Polaris.Polaris.Organizations

So unless you’ve memorized all those codes, which is

totally possible after you’ve used these tables for a while, you’ll

want to JOIN some tables into your NotificationsLog queries for

more readable results. Taking a look at the DeliveryOptions table,

we see that DeliveryOptionIDs 3, 4, and 5 are used for Phone 1,

Phone 2, and Phone 3 respectively. I like to use all three, just to

make sure that I get all of the telephony activity, but you can a

single phone number designation if you like. That in mind, let’s

take a look at what went on last time Polaris called this patron:

SELECT

PatronID,

NotificationTypeID,

DeliveryOptionID,

Details

FROM

86

Daniel Messer

PolarisTransactions.Polaris.NotificationLog WITH

(NOLOCK)

WHERE

PatronID = 4886

AND

NotificationDateTime BETWEEN '2023-08-19

00:00:00.000' AND '2023-08-21 23:59:59.999'

ORDER BY

NotificationDateTime DESC

Right, from what we can see here, five phone calls were

attempted and all of them came up busy. This Polaris system is

setup to try calling a patron five times and, if it can’t get through,

it’ll roll the patron over to print notifications. So in this case, we

could tell the patron that the library tried to call, but couldn’t ever

get through because someone was on the phone, or at least because

the line was busy.

If you want to see if an email went out, you’d simply

modify the DeliveryOptionID to 2. That in mind, here’s a query

that will pull friendly, readable results for a given patron’s

notification history.

SELECT

nl.NotificationDateTime AS [Notification Date],

nl.PatronID AS [Patron ID],

nl.PatronBarcode AS [Patron Barcode],

pr.PatronFullName AS [Patron Name],

o.Name AS [Notification Libary\Branch],

nt.Description AS [Notification],

do.DeliveryOption AS [Delivery Option],

nl.DeliveryString AS [Delivery String]

FROM

Bricks Without Clay

87

PolarisTransactions.Polaris.NotificationLog nl

WITH (NOLOCK)

INNER JOIN

Polaris.Polaris.PatronRegistration pr WITH

(NOLOCK)

ON (pr.PatronID = nl.PatronID)

INNER JOIN

Polaris.Polaris.Organizations o WITH (NOLOCK)

ON (o.OrganizationID = nl.ReportingOrgID)

INNER JOIN

Polaris.Polaris.NotificationTypes nt WITH

(NOLOCK)

ON (nt.NotificationTypeID =

nl.NotificationTypeID)

INNER JOIN

Polaris.Polaris.DeliveryOptions do WITH (NOLOCK)

ON (do.DeliveryOptionID = nl.DeliveryOptionID)

WHERE -- Adjust these dates however you like

nl.NotificationDateTime BETWEEN '2023-08-01' AND

'2023-08-31'

ORDER BY

nl.NotificationDateTime DESC￼

Moving on to the EmailLog table, this one seems redundant

at first, given there’s a NotificationLog, right? Well maybe, but this

table is there to keep track of eReceipts that have been emailed to

patrons for checkouts and payments. The column names are pretty

straightforward, and this is the most self-contained table in

PolarisTransactions.

PatronID – The patron’s unique identification number.

88

Daniel Messer

OrganizationID – The OrganizationID of the branch or library

sending the notification.

FromAddr – The email address the eReceipt was sent from.

ToAddr – The email address the eReceipt was sent to, which

should be the email address(es) on the patron’s registration.

EmailSubject – The subject line of the email. This is probably the

easiest way to identify what find of eReceipt was sent.

SendDate – The date and time the eReceipt was emailed.

EmailType – As I write this, there is only one EmailType and that

is 1, which stands for circulation receipt.

Succeeded – Did the email successfully go out? 1 for yes and 0 for

no. Remember, this only tells you if Polaris was able to send the

email. It knows nothing about whether it was delivered

successfully.

Most of the time, working with this table is a cake walk

compared to the rest of the PolarisTransactions database. You’re

usually looking for a PatronID or patron email, what was sent, and

when. And since there’s no reason to deal with numerical codes if

you don’t need to. This query will pull a readable result list for a

given PatronID.

SELECT

el.PatronID AS [Patron ID],

pr.PatronFullName AS [Patron Name],

o.Name AS [Sending Library/Branch],

el.FromAddr AS [Sent From],

el.ToAddr AS [Sent To],

el.EmailSubject AS [Subject],

el.SendDate AS [Sent Date]

FROM

Bricks Without Clay

89

PolarisTransactions.Polaris.EmailLog el WITH

(NOLOCK)

INNER JOIN

Polaris.Polaris.PatronRegistration pr WITH

(NOLOCK)

ON (pr.PatronID = el.PatronID)

INNER JOIN

Polaris.Polaris.Organizations o WITH (NOLOCK)

ON (o.OrganizationID = el.OrganizationID)

WHERE -- Drop in your PatronID here

el.PatronID = 111

ORDER BY

el.SendDate DESC

90

Daniel Messer

￼And with that, we’ve almost come to the end of our walk

through the wilds of the PolarisTransactions database! We’ll check

in one more time for a wrap up along with some tips and tricks for

what to do next, but I wanted to take a few words and thank you so

much for reading this far! I hope this short guide has been helpful

and made your life a bit easier when you’re faced with drawing

down the data from PolarisTransactions. Like Camelot,

PolarisTransactions is a silly place, but it’s also rather like a forest.

You can look at the forest, you can look at individual trees and,

old-timey wisdom about forests and trees notwithstanding, you can

see the connections between both.

Bricks Without Clay

91

Chapter Eleven - Where My Story

Ends and Yours Begins

The secret of a good sermon is to have a good beginning and a

good ending, then having the two as close together as possible.

~George Burns

Alice in Wonderland, Alice down the rabbit hole, Alice out in

Cyberspace, flung along the lines of data, flying across fields of

light, the night cities that live only behind her eyes.

~Melissa Scott, Trouble and her Friends￼

O

ver the last ten chapters we've experienced the wonders of

the PolarisTransactions database. It is a place of wonders,

even if, sometimes, you're just wondering how it works.

It's not a perfect database, such things don't exist except in the

fevered dreams of cyberpunk and science fiction authors and, all

too often, those databases gain sentience and try to take over the

world or something. Whereas you, you just needed to know how

many non-fiction items were checked out from a given branch in

92

Daniel Messer

the last 45 days. But speaking of wonders, now that we've reached

the end, you may be wondering "Okay, now what?"

Well, that's up to you and your needs.

My job, and my skill set, finds me spending a great deal of

time wading through PolarisTransactions looking for simple and

complex data. You may only find yourself in there as a matter of

necessity, a lonely SQL coder trying to make sense of it all. And on

that note, if there's anything I want to leave you with, it's a story.

Specifically: your story.

Data is messy. You can organize it, and that's helpful. You

can present it to someone and that's informational. And you can do

a lot by simply naming your columns something other than

"PatronID" or "BrowseTitle." Organization and presentation, that

stuff is important, but what I advise you to do is find the story in

the data. There's one there, and I admit it may not be a very

inspiring story or an edge-of-your-seat thriller. But people,

especially librarians, love stories and we've been telling them and

listening to them for years. Stories help us make sense of chaos,

and there's a rightful sense of chaos in the PolarisTransactions

database.

So when you're digging for that data, keep in mind who

wants it. Maybe they don't need a story and, heck, maybe you are

the person who needs the data and you're only after a result list.

That's fine. But as an example, the Director comes around and

wants to know how the Lakeshore Branch is doing when it comes

to the circulation of children's materials by patrons over the age of

twenty. Take a moment to step back and maybe ask some follow up

questions. Patrons over the age of twenty? Checking out children's

materials? Ah, the Director is likely interested in parents checking

out items for their kids. Confirm that, dial in the parameters.

Bricks Without Clay

93

It's like a reference interview. Any Reference Librarian

who's worked the desk longer than two days will tell you that

patrons don't ask for what they actually want. The patron who

wants information about traveling in Denmark? No, he wants to

know about pubs in Copenhagen, but he didn't ask that right away.

It's called a reference interview for a reason, because there's a

back-and-forth and follow up in the questioning.

Another question you can ask is, okay, how is Lakeshore

Branch performing? Right, but... performing compared to what? I

could walk up to open mic night at the local gastropub and start

telling jokes. I might even get some laughs. Those laughs are data

points and, based upon my abilities, those are interesting results

about my comedy routine. But if, when I'm done, Hannah Gadsby

walks up and kicks off her own set... suddenly the story of my

stand up performance takes a drastic turn. Because it now exists in

comparison to that of a professional stand-up comedian.

Lakeshore Branch is, hopefully, moving quite a few

children's materials and parents are checking them out. Great!

But... what's a lot? A lot of children's materials compared

to adult fiction circulation at Lakeshore? Or a lot of children's

materials compared to the same circulation parameters at the Main

Library? The circ stats at the Main dwarf Lakeshore's because

Lakeshore is a small branch and the Main has multiple floors.

Maybe that sounds like an apples to oranges comparison but if you

look at the percentages, you come back to comparing the same

fruit. Lakeshore is moving, say, 48% of their children's materials

out the door though the hands of parents. But at Main — sure,

they're circulating more items — but only 32% of those children's

items are checked out by people over the age of 20.

Now you have a story. What that story tells you, well, that's

up to you and your library.

94

Daniel Messer

At the very least, I want to thank you for reading this story.

I hope you've enjoyed the randomness, the jokes, and maybe found

something that will help turn your slog through

PolarisTransactions into a casual walk. I encourage you to check

out the Innovative Users Group Forum website, a clearing house

and conversational hub of librarians who are better at working the

Polaris databases than I am. The only reason they didn't write this

book is they're too busy with other things, and one of those things

is helping others. Show up, log in, read along, and introduce

yourself. Try new things and ask questions.

We're librarians, we love questions.

I hope you find some answers to yours.

https://forum.innovativeusers.org/

Bricks Without Clay

95

96

Daniel Messer

￼

About the Author￼

Daniel Messer is a public librarian who specializes in

integrated library systems, shadow libraries, library technology,

and circulation methodology. He’s a pulp nonfiction writer with

books about the surprising links between historical events,

cyberpunk and online culture, and the paranormal. He’s created

several podcasts and runs the Cyberpunk Librarian podcast

network where you’ll find podcasts about libraries and

technology, popular culture, history, and the paranormal.

Beyond writing, he’s a musician working in space and

ambient music under the name Euphemystique. All of his creative

projects are released from Tanglewood Hill Studio, his digital

warehouse of whatever-the-hell he’s working on now.

He lives in Kentucky with his family and two dogs.

1. Dedication

2. Prologue

3. Chapter One - Waypoints

4. Chapter Two - The View from the Bottom: TransactionHeaders

5. Chapter Three - Just the Facts, Ma’am: TransactionTypes

6. Chapter Four - Cookbook: Part One

7. Chapter Five - The Devil, the Data, and the Deep Blue Details

8. Chapter Six - Cookbook: Part Two

9. Chapter Seven - The Codes Under the Codes Beneath the Type

10. Chapter Eight - String Theory

11. Chapter Nine - Cookbook: Part Three

12. Chapter Ten - Polaris Calling

13. Chapter Eleven - Where My Story Ends and Yours Begins

14. About the Author

1. 2

2. 3

3. 4

4. 5

5. 6

6. 7

7. 8

8. 9

9. 10

10. 11

11. 12

12. 13

13. 14

14. 15

15. 16

16. 17

17. 18

18. 19

19. 20

20. 21

21. 22

22. 23

23. 24

24. 25

25. 26

26. 27

27. 28

28. 29

29. 30

30. 31

31. 32

32. 33

33. 34

34. 35

35. 36

36. 37

37. 38

38. 39

39. 40

40. 41

41. 42

42. 43

43. 44

44. 45

45. 46

46. 47

47. 48

48. 49

49. 50

50. 51

51. 52

52. 53

53. 54

54. 55

55. 56

56. 57

57. 58

58. 59

59. 60

60. 61

61. 62

62. 63

63. 64

64. 65

65. 66

66. 67

67. 68

68. 69

69. 70

70. 71

71. 72

72. 73

73. 74

74. 75

75. 76

76. 77

77. 78

78. 79

79. 80

80. 81

81. 82

82. 83

83. 84

84. 85

85. 86

86. 87

87. 88

88. 89

89. 90

90. 91

91. 92

92. 93

93. 94

94. 95

95. 96

1. Page 1